IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetKeyContacts]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetKeyContacts]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
             
CREATE PROCEDURE [dbo].[GetKeyContacts]            
    
@inst varchar(50),  
@chiefCode varchar(50),
@sponsorTypeId varchar(50)
    
AS            
BEGIN              
 SET NOCOUNT ON      
    
DECLARE @intSponsorTypeId INT 
IF @sponsorTypeId is not null 
  SET @intSponsorTypeId = CONVERT(INT,@sponsorTypeId)
       
  
Select   
RFI.AdmPersonId,  
FDO.Institution,               
FDO.ChiefCode,  
RFI.RoleId,  
RT.Name as RoleName,  
AP.FirstName + space(2) + AP.LastName as FullName,  
ISNULL((Select top 1 ATN.Number from dbo.AdmTelephoneNumber ATN WHERE AdmTelephoneTypeId = 1 AND ATN.AdmPersonId = AP.Id ),'')as phone,  
ISNULL((Select top 1 EA.emailaddress from dbo.admemailaddress EA WHERE EA.admemailaddresstypeid = 1 AND EA.AdmPersonId = AP.Id ),'')as Email  
  
FROM dbo.AdmResearchFinanceInfo RFI    
INNER JOIN dbo.FwkDomainOrganization FDO ON FDO.Id = RFI.FwkDomainOrganizationId    
INNER JOIN dbo.AdmPerson AP ON AP.Id = RFI.AdmPersonId      
INNER JOIN dbo.admRoleTypes RT ON RT.RoleId = RFI.RoleId   
AND LTRIM(RTRIM(FDO.Institution))= LTRIM(RTRIM(@inst))  
AND LTRIM(RTRIM(FDO.ChiefCode))= LTRIM(RTRIM(@chiefCode)) 
AND (RFI.AdmSponsorTypeId = @intSponsorTypeId OR  RFI.AdmSponsorTypeId=16)
  
 

  
  
                
 SET NOCOUNT OFF              
END   
GO
